#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   stock_hist.py
@Time    :   2022/11/24 21:50:19
@Author  :   youle
@Email   :   a_fly0505@163.com
@Copyright : 侵权必究
'''
import akshare as ak
import psycopg2
import pandas as pd
import datetime
from core.config import Config
import sys
import math
import core.utils as utils
sys.path.append("..")

# 数据库连接参数
# dbname: the database name
# database: the database name (only as keyword argument)
# user: user name used to authenticate
# password: password used to authenticate
# host: database host address (defaults to UNIX socket if not provided)
# port: connection port number (defaults to 5432 if not provided)

create_stock_table = '''CREATE TABLE IF NOT EXISTS stock_datas.stock_{}_{}
(
    id SERIAL PRIMARY KEY NOT NULL,
    date date NOT NULL UNIQUE,
    open real NOT NULL,
    close real NOT NULL,
    hight real NOT NULL,
    low real NOT NULL,
    volume double precision DEFAULT 0.0,
    volume_quota double precision DEFAULT 0.0,
    vibration real,
    rise_fall_rate real DEFAULT 0.0,
    rise_fall_quota real DEFAULT 0.0,
    turnover_rate real
);'''

insert_stock_table = '''INSERT INTO stock_datas.stock_qfq_{}(
	date, open, close, hight, low, volume, volume_quota, vibration, rise_fall_rate, rise_fall_quota, turnover_rate)
	VALUES ('{}', {}, {}, {}, {}, {}, {}, {}, {}, {}, {});'''

select_stock_table = '''SELECT id,date FROM stock_datas.stock_qfq_{} ORDER BY date DESC LIMIT {};'''

query_table_exist = '''select count(*) from pg_class where relname = 'stock_qfq_{}';'''
query_last_date = '''SELECT date FROM stock_datas.stock_qfq_{} ORDER BY date DESC LIMIT 1;'''

delete_by_date = '''DELETE FROM stock_datas.stock_qfq_{} WHERE date = '{}';'''

INIT_SCHEMA = '''
CREATE SCHEMA IF NOT EXISTS stock_market AUTHORIZATION postgres;
CREATE SCHEMA IF NOT EXISTS stock_datas AUTHORIZATION postgres;
'''


def GetExchageByCode(code: str) -> str:
    if code.startswith("60") or code.startswith("68"):
        return "sh"
    elif code.startswith("00") or code.startswith("30"):
        return "sz"
    else:
        return "bj"


class StockHist:
    def __init__(self, config: Config) -> None:
        self.conn = psycopg2.connect(database=config.database,
                                     user=config.user, password=config.password,
                                     host=config.host, port=config.port)
        self.cur = self.conn.cursor()

    def InitDB(self) -> bool:
        try:
            self.cur.execute(INIT_SCHEMA)
            self.conn.commit()
            return True
        except Exception as e:
            print('数据库commit：{}'.format(e))
            return False


    def __createStockTable(self, code: str) -> bool:
        try:
            exec_sql = create_stock_table.format("qfq", code)
            self.cur.execute(exec_sql)
            self.conn.commit()
        except Exception as e:
            print('create stock {} table errmsg:{}'.format(code, e))
            return False

    def __insertDataToDB(self,
                         code: str,
                         df: pd.DataFrame) -> bool:
        try:
            for item in df.values:
                exec_sql = insert_stock_table.format(code,
                                               item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9], item[10])
                self.cur.execute(exec_sql)
            self.conn.commit()
            return True
        except Exception as e:
            print("catch exception: ", e)
            return False

    def GetAllStockInfo(self) -> None:
        """
        @description: 获取全部沪深京股票名称，代码
        @param: 无
        @Returns: 无 
        """
        stock_df = ak.stock_zh_a_spot_em()
        print(stock_df.columns)
        discard = []
        try:
            # 遍历每只股票
            # 科创板属于上交所(68开头),创业板属于深交所(30开头)
            # 北交所的股票8字开头，包括82、83、87、88. 其中82开头的股票表示优先股, 83和87开头的股票表示普通股票、88开头的股票表示公开发行的
            for item in stock_df.values:
                code:str = item[1]
                sname = item[2]
                if "ST" in sname or "退" in sname:
                    print("not create %s", sname)
                    discard.append(sname)
                    continue
                exchange = ""
                if code.startswith("60") or code.startswith("68"):
                    exchange = "sh"
                elif code.startswith("00") or code.startswith("30"):
                    exchange = "sz"
                else:
                    exchange = "bj"
                sql = '''INSERT INTO stock_market.stock(code, sname, exchange) VALUES (%s, %s, %s);'''
                datas = [code, sname, exchange]
                self.cur.execute(sql, datas)
            self.conn.commit()
            print("=====", discard)
        except Exception as e:
            print('数据库commit：{}'.format(e))

    def GetStockDailyHist(self,
                          code: str,
                          type: str,
                          index: int,
                          start_date: str,
                          end_date: str = "20500101",
                          ) -> pd.DataFrame:
        """
        :type: 是沪，深，京
        :index: % 用东财，同花顺，新浪,网易，腾讯，百度
        """
        # 东财
        return ak.stock_zh_a_hist(code, "daily", start_date, end_date, adjust="qfq")

    # #新浪
    # return ak.stock_zh_a_daily(type+code, start_date, end_date, adjust="qfq")

    def GetAllStockHist(self):
        """
        @description: 获取所有股票最新历史数据
        @param: 
        @Returns: 
        """

        self.cur.execute("SELECT code, sname, exchange FROM stock_market.stock")
        self.conn.commit()
        datas = self.cur.fetchall()
        for item in datas:
            # time.sleep(0.05)
            code = item[0]
            sname = item[1]
            exchange = item[2]
            # 如果不存在此股票的表，创建
            self.__createStockTable(code=code)
            # 查询此股票的最后一次日期
            exec_sql = select_stock_table.format(code, 1)
            self.cur.execute(exec_sql)
            self.conn.commit()
            oneData = self.cur.fetchall()
            beginDate = "19700101"
            if len(oneData) > 0:
                lastDate = oneData[0][1]
                deltaday = datetime.date.today() - lastDate
                if deltaday.days <= 0:
                    continue
                lastDate = lastDate+datetime.timedelta(days=1)
                beginDate = lastDate.strftime("%Y%m%d")
            print("get stock info begin date: ", code, sname, beginDate)
            df = self.GetStockDailyHist(code, exchange, 1, beginDate)
            self.__insertDataToDB(code, df)

    def SyncStockEveryDay(self):
        """
        0  | 序号      | int64   | -       |
        1  | 代码      | object  | -       |
        2  | 名称      | object  | -       |
        3  | 最新价     | float64 | -       |
        4  | 涨跌幅     | float64 | 注意单位: % |
        5  | 涨跌额     | float64 | -       |
        6  | 成交量     | float64 | 注意单位: 手 |
        7  | 成交额     | float64 | 注意单位: 元 |
        8  | 振幅      | float64 | 注意单位: % |
        9  | 最高      | float64 | -       |
        10 | 最低      | float64 | -       |
        11 | 今开      | float64 | -       |
        12 | 昨收      | float64 | -       |
        13 | 量比      | float64 | -       |
        14 | 换手率     | float64 | 注意单位: % |
        15 | 市盈率-动态  | float64 | -       |
        16 | 市净率     | float64 | -       |
        17 | 总市值     | float64 | 注意单位: 元 |
        18 | 流通市值    | float64 | 注意单位: 元 |
        19 | 涨速      | float64 | -       |
        20 | 5分钟涨跌   | float64 | 注意单位: % |
        21 | 60日涨跌幅  | float64 | 注意单位: % |
        22 | 年初至今涨跌幅 | float64 | 注意单位: % |
        """
        stock_df = ak.stock_zh_a_spot_em()
        today = datetime.date.today()
        for stock in stock_df.values:
            code = stock[1]
            sname = stock[2]
            #查询表是否存在
            query_sql = query_table_exist.format(code)
            self.cur.execute(query_sql)
            self.conn.commit()
            raw = self.cur.fetchone()
            if raw[0] == 0:
                continue

            query_sql = query_last_date.format(code)
            self.cur.execute(query_sql)
            self.conn.commit()
            raw = self.cur.fetchone()
            if raw is None or len(raw) <= 0:
                # to log
                continue
            lastDate = raw[0]
            if today == lastDate:
                # to log
                continue

            isOneDay = utils.IsOneDay(today, lastDate)
            if not isOneDay:
                lastDate = lastDate+datetime.timedelta(days=1)
                beginDate = lastDate.strftime("%Y%m%d")
                print("get stock info begin date: ", code, sname, beginDate)
                df = self.GetStockDailyHist(code, GetExchageByCode(code), 1, beginDate)
                self.__insertDataToDB(code, df)
            else:
                date = today.strftime("%Y%m%d")
                open = stock[11]
                if math.isnan(open):
                    print("code {} name {} open is nan 可能停盘".format(code, sname))
                    continue
                close = stock[3]
                high = stock[9]
                low = stock[10]
                volume = stock[6]
                volume_quota = stock[7]
                vibration = stock[8] 
                rise_fall_rate = stock[4] 
                rise_fall_quota = stock[5]
                turnover_rate = stock[14]
                print("get stock info begin date: ", code, sname, date)
                exec_sql = insert_stock_table.format(
                    code, date, open, close, high, low, volume, volume_quota, vibration, rise_fall_rate, rise_fall_quota, turnover_rate)
                self.cur.execute(exec_sql)
                self.conn.commit()

    def DeleteStockByDay(self, dayStr:str):
        self.cur.execute("SELECT code, sname FROM stock_market.stock")
        datas = self.cur.fetchall()
        for item in datas:
            code = item[0]
            sname = item[1]
            #查询股票表是否存在
            query_sql = query_table_exist.format(code)
            self.cur.execute(query_sql)
            self.conn.commit()
            raw = self.cur.fetchone()
            if raw[0] == 0:
                continue
            delete_sql = delete_by_date.format(code, dayStr)
            self.cur.execute(delete_sql)
            self.conn.commit()
            print("delete {} date={}".format(code, dayStr))

